﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class T_MaintainPage : System.Web.UI.Page
{
    //public string studentid; //用于保存表格修改之前的id，以便表格修改之后在数据库中找到相应行
    public static DataTable ta = new DataTable();//用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();  //用于数据绑定
    public static string accnum;           //登录账号
    public static string role;             //登录身份

    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";    //分页处理
        btnPrev.Text = "前一页";
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";

        if (!IsPostBack)
        {
            accnum = (string)Session["AccNum"];
            role = (string)Session["role"];
            string sql = "select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.id as semesterid,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' order by STU_INFO.id ;";
            try
            {
                ExecuteSQL(sql);
            }
            catch
            { 
            
            }

        }
    }
    void ExecuteSQL(string sql)
    {
        //string sql = "select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.id as semesterid,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' order by STU_INFO.id ;";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ta.Columns.Clear();
        ta.Rows.Clear();
        da.Fill(ta);
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();
        myConn.Close();
    }
    public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs e)//datagrid中点击编辑edit时执行函数
    {

        vi = new DataView(ta);                                          //从ta中获取数据源用于绑定

        dg1.EditItemIndex = e.Item.ItemIndex;
        dg1.DataSource = vi;
        dg1.DataBind();

    }

    public void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs e) //处于可编辑状态时，点击cancel执行函数
    {

        dg1.EditItemIndex = -1;
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();

    }

    public void MyDataGrid_Update(Object sender, DataGridCommandEventArgs e) //处于可编辑状态时，修改表格数据后，点击update执行函数
    {

        int index = dg1.CurrentPageIndex * dg1.PageSize + e.Item.ItemIndex;    //获取修改行的索引号(需要加上分页计算)

       
        string courseid = e.Item.Cells[1].Text;               //此处获取表格中修改行的所有数据，以便下面程序删除该行，重新增加新行
        string coursename = e.Item.Cells[2].Text; 
        string studentid = e.Item.Cells[3].Text;
        string studentname = e.Item.Cells[4].Text;
        string classname = e.Item.Cells[5].Text; 
        TextBox markText = (TextBox)e.Item.Cells[6].Controls[0]; 
        string semesterid = e.Item.Cells[7].Text;
        string semestername = e.Item.Cells[8].Text;

        string mark = markText.Text;


        
        DataRow dr = ta.NewRow();                  //创建一个ta类型中的新行

        dr[0] = courseid; dr[1] = coursename; dr[2] = studentid; dr[3] = studentname; dr[4] = classname; dr[5] = mark; dr[6] = semesterid; dr[7] = semestername;
      
        ta.Rows.RemoveAt(index);              //删除ta中的旧行，添加新行
        ta.Rows.InsertAt(dr, index);

        vi = new DataView(ta);               //重新数据绑定vi
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();


        //对数据库操作，更新数据，此处会用到上面保存的studentid
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("update CLASS_INFO set mark='" + dr[5].ToString() + "' where CLASS_INFO.studentid='" + studentid + "' and CLASS_INFO.semesterid='"+semesterid+"' and CLASS_INFO.courseid='"+courseid+"';", myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();




    }

    public void MyDataGrid_Delete(Object sender, DataGridCommandEventArgs e)  //datagrid中删除行时执行函数
    {

        int index = dg1.CurrentPageIndex * dg1.PageSize + e.Item.ItemIndex;             //获取被删除行的索引
       
        string courseid = e.Item.Cells[1].Text;               //保存主键id
        string studentid = e.Item.Cells[3].Text;
        string semesterid = e.Item.Cells[7].Text;

        ta.Rows.RemoveAt(index);                  //删除旧行

        vi = new DataView(ta);                    //重新绑定
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();

        //更新数据库数据
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("update CLASS_INFO set state='0' where CLASS_INFO.studentid='" + studentid + "'and CLASS_INFO.semesterid='"+semesterid+"' and CLASS_INFO.courseid='"+courseid+"';", myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();

    }

    private void ShowStats()  //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }
    string ReaderValue(string sql)
    {
        string value = "";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataReader myReader = cmd.ExecuteReader();
        int i = 0;
        while (myReader.Read())
        {
            value = myReader[0].ToString().Trim();
            i++;
        }
        myReader.Close();
        myConn.Close();
        return value;
    }
    void Execute(string sql)
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();
        myConn.Close();
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string sql = "";
        if (TextBox1.Text == "")
        {
            Alert("请输入课程名");
        }
        else
        {
            if (TextBox2.Text == "")
            {
                Alert("请输入学号");
            }
            else
            {
                if (DL1.Text == "请选择学期")
                {
                    Alert("请选择学期");
                }
                else
                {
                    //缺少不存在返回空值处理
                    sql = "select distinct CLASS_INFO.courseid from CLASS_INFO,COURSE_INFO,SEM_INFO where SEM_INFO.id=CLASS_INFO.semesterid and SEM_INFO.name='"+DL1.Text+"' and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.name='"+TextBox1.Text+"';";
                    string courseid = ReaderValue(sql);
                    sql = "select id from SEM_INFO where name='"+DL1.Text+"';";
                    string semesterid = ReaderValue(sql);
                    sql = "insert into CLASS_INFO VALUES('" + semesterid + "','" + courseid + "','" + TextBox2.Text + "','','1');";
                    Execute(sql);
                    sql = "select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.id as semesterid,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' order by STU_INFO.id ;";
                    ExecuteSQL(sql);

                }
            }
        }
    }
    protected void LinkButton1_Click(object sender, EventArgs e)
    {

    }
    void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }
    protected void Button2_Click(object sender, EventArgs e)//下载功能尚未实现
    {

    }
}
